import sqlite3
import schemas
from data_models import SQLTableRow, COLUMN_DEFINITIONS
from config import DB_FILE, TABLE_NAME

def create_table(db_name : str, table_name : str, columns : list[SQLTableRow]) -> None:
    """
    Creates a table in the specified SQLite database.
    Args:
        db_name (str): The name of the SQLite database file.
        table_name (str): The name of the table to create.
        columns (str): A string representing the column definitions (e.g., "id INTEGER PRIMARY KEY, name TEXT").
    """
    columns_str = ', '.join([f'"{col["name"]}" {col["type"]}' for col in columns])
    try:
        conn = sqlite3.connect(db_name)
        cursor = conn.cursor()
        cursor.execute(f"DROP TABLE IF EXISTS {table_name};")  # Drop table if it exists
        create_table_sql = f"CREATE TABLE {table_name} ({columns_str});"
        cursor.execute(create_table_sql)
        conn.commit()
        print(f"Table '{table_name}' created successfully in '{db_name}'.")
    except sqlite3.Error as e:
        print(f"An error occurred: {e}")
    finally:
        if conn:
            conn.close()

def insert_data(data: list[SQLTableRow]) -> None:
    try:
        conn = sqlite3.connect(DB_FILE)
        cursor = conn.cursor()
        # Ensure the table exists before inserting data
        create_table(DB_FILE, TABLE_NAME, COLUMN_DEFINITIONS)

        # Prepare the SQL insert statement
        columns = ', '.join([col['name'] for col in COLUMN_DEFINITIONS])
        placeholders = ', '.join(['?'] * len(COLUMN_DEFINITIONS))
        insert_sql = f"INSERT INTO {TABLE_NAME} ({columns}) VALUES ({placeholders})"

        # Insert each row
        for row in data:
            values = [
                row.server,
                row.database,
                row.schema,
                row.table_name,
                row.table_ddl
            ]
            # Ensure the values match the order of the columns
            if len(values) != len(COLUMN_DEFINITIONS):
                raise ValueError("The number of values does not match the number of columns.")
            # Execute the insert statement
            cursor.execute(insert_sql, values)
        
        conn.commit()
        print(f"Data inserted successfully into '{TABLE_NAME}' in '{DB_FILE}'.")
    except sqlite3.Error as e:
        print(f"An error occurred: {e}")
    finally:
        if conn:
            conn.close()

def main():
    """
    Main function to extract all SQLTableRow instances from schemas and insert them into the database.
    """
    data_to_insert = [
        item for name, item in schemas.__dict__.items()
        if isinstance(item, SQLTableRow)
    ]
    if data_to_insert:
        insert_data(data_to_insert)
    else:
        print("No data to insert.")

if __name__ == "__main__":
    main()